package demo.database.show;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import com.titan.core.utils.db.DBUtils;
import com.titan.core.utils.db.RecordSet;

/**
 *
 *
 * @author 周成功
 * 
 */
public class ShowTableInfo {
    public static void main(String[] args) {
        String[][] config = new String[][] {
//                {"oracle", "127.0.0.1", "1521", "ORCL", "etms3_base", "etms3_base"},
//                {"oracle", "127.0.0.1", "1521", "ORCL", "etms3", "etms3"}
//                {"oracle", "10.54.121.250", "1521", "CNNPS", "nbas", "nbas"}
                {"mssql", "192.168.128.213", "1433", "H20170913", "sa", "123456"}
//                {"oracle", "192.168.128.212", "1521", "ORCL", "hrbsgts", "hrbsgts"}
//            {"mysql", "127.0.0.1", "3306", "play", "root", "123456"}
        };
        
        Connection conn = null;
        try {
            for (int i = 0; i < config.length; i++) {
                String sql = null;
                if("oracle".equalsIgnoreCase(config[i][0])) {
                    sql = "select t.tname from tab t where t.tabtype='TABLE' and tname not in (SELECT object_name FROM user_recyclebin)";
                    conn = DBUtils.getOracleConnection(config[i][1], config[i][2], config[i][3], config[i][4], config[i][5], true);
                } else if("mysql".equalsIgnoreCase(config[i][0])) {
                    sql = "select table_name tname, table_comment from information_schema.tables where upper(table_schema) = upper('"+config[i][3]+"') and upper(table_type)='BASE TABLE'";
                    conn = DBUtils.getMySQLConnection(config[i][1], config[i][2], config[i][3], config[i][4], config[i][5], true);
                } else if("mssql".equalsIgnoreCase(config[i][0])) {
                    sql = "select name tname from sys.tables";
                    conn = DBUtils.getMSSQLConnection(config[i][1], config[i][2], config[i][3], config[i][4], config[i][5], true);
                } else {
                    throw new RuntimeException("不知道的数据库类型["+config[i][0]+"]！");
                }
                
                RecordSet record = DBUtils.executeQueryToRecordSet(conn, sql);
                List<String> result = new ArrayList<String>();
                while(record.moveNext()) {
                    String tablename = record.getString("tname");
                    System.out.println(tablename);
                    
                    String select = null;
                    if("mssql".equalsIgnoreCase(config[i][0])) {
                        select = "select * from [" + tablename + "]";
                    } else {
                        select = "select * from " + tablename + "";
                    }
                    
                    PreparedStatement stmt = null;
                    ResultSet rest = null;
                    try {
                        stmt = conn.prepareStatement(select);
                        rest = stmt.executeQuery();
                        ResultSetMetaData data = rest.getMetaData();
                        for (int j = 1; j <= data.getColumnCount(); j++) {
                         // 获得所有列的数目及实际列数
                            int columnCount = data.getColumnCount();
                            // 获得指定列的列名
                            String columnName = data.getColumnName(j);
                            // 获得指定列的列值
                            int columnType = data.getColumnType(j);
                            // 获得指定列的数据类型名
                            String columnTypeName = data.getColumnTypeName(j);
                            // 所在的Catalog名字
                            String catalogName = data.getCatalogName(j);
                            // 对应数据类型的类
                            String columnClassName = data.getColumnClassName(j);
                            // 在数据库中类型的最大字符个数
                            int columnDisplaySize = data.getColumnDisplaySize(j);
                            // 默认的列的标题
                            String columnLabel = data.getColumnLabel(j);
                            // 获得列的模式
                            String schemaName = data.getSchemaName(j);
                            // 某列类型的精确度(类型的长度)
                            int precision = data.getPrecision(j);
                            // 小数点后的位数
                            int scale = data.getScale(j);
                            // 获取某列对应的表名
                            String tableName = data.getTableName(j);
                            // 是否自动递增
                            boolean isAutoInctement = data.isAutoIncrement(j);
                            // 在数据库中是否为货币型
                            boolean isCurrency = data.isCurrency(j);
                            // 是否为空
                            int isNullable = data.isNullable(j);
                            // 是否为只读
                            boolean isReadOnly = data.isReadOnly(j);
                            // 能否出现在where中
                            boolean isSearchable = data.isSearchable(j);
                            System.out.println(columnCount);
                            System.out.println("获得列" + j + "的字段名称:" + columnName);
                            System.out.println("获得列" + j + "的类型,返回SqlType中的编号:"+ columnType);
                            System.out.println("获得列" + j + "的数据类型名:" + columnTypeName);
                            System.out.println("获得列" + j + "所在的Catalog名字:"+ catalogName);
                            System.out.println("获得列" + j + "对应数据类型的类:"+ columnClassName);
                            System.out.println("获得列" + j + "在数据库中类型的最大字符个数:"+ columnDisplaySize);
                            System.out.println("获得列" + j + "的默认的列的标题:" + columnLabel);
                            System.out.println("获得列" + j + "的模式:" + schemaName);
                            System.out.println("获得列" + j + "类型的精确度(类型的长度):" + precision);
                            System.out.println("获得列" + j + "小数点后的位数:" + scale);
                            System.out.println("获得列" + j + "对应的表名:" + tableName);
                            System.out.println("获得列" + j + "是否自动递增:" + isAutoInctement);
                            System.out.println("获得列" + j + "在数据库中是否为货币型:" + isCurrency);
                            System.out.println("获得列" + j + "是否为空:" + isNullable);
                            System.out.println("获得列" + j + "是否为只读:" + isReadOnly);
                            System.out.println("获得列" + j + "能否出现在where中:"+ isSearchable);                            
                        }
                    } finally {
                        DBUtils.close(rest);
                        DBUtils.close(stmt);
                    }
                }
                DBUtils.close(conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn);
        }
    }

}

